/*
	EXEC sp_load_all_order_schedule null, null, null, null
*/
ALTER    proc sp_load_all_order_schedule
-- Input parameter is process StepID;
@Product_ID varchar(15),
@Customer_ID varchar(15),
@Sales_Order_No varchar(15),
@so_booking_year varchar(15)

as
declare @SQLString NVARCHAR(2000)
declare @ParamDefinition NVARCHAR(500)
declare @results Table (LineItem_ID int, SalesOrder_No varchar (50), 
	COOL_Requested_Date datetime, Schedule_Date datetime,COOL_Qty int, COOL_Unit_Cost float,
	Status varchar (20),COOL_Line_Item_No int, Product_ID int,
	COOL_Sugg_Schedule_Date_With_Substitutables datetime,COOL_Sugg_Schedule_Date_Without_Substitutables datetime, SalesOrder_ID int,
	COOL_Amount float,Customer_ID int, Customer_PartNumber varchar(50), SO_Booking_Date datetime, SO_ODM_ID int)

	-- added by prabhanjan on 28th october 2003 to provide additional search, based on sales order number
	-- uses sp_executesql statement to dynamically create the cursor.
	set @SQLString = N'declare lineItemCursor CURSOR for select cool.lineitem_id, sales_order.salesorder_no, cool.cool_requested_date, ISNULL(COOL.COOL_REVISED_DATE,cool.cool_schedule_date), cool.cool_qty, cool.cool_unit_cost, cool.COOL_Status, cool.COOL_Line_Item_No, cool.Product_ID, cool.COOL_Sugg_Schedule_Date_With_Substitutables, cool.COOL_Sugg_Schedule_Date_Without_Substitutables, cool.COOL_CUSTOMER_PARTNUMBER, sales_order.SalesOrder_ID, cool.Cool_Amount, sales_order.Customer_ID, sales_order.SO_Booking_Date, Sales_Order.SO_ODM_ID, (SELECT isnull( SUM( CSLIT_Qty_Shipped ), 0 ) AS SHIPPED_QTY  FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LineItem_ID = cool.lineitem_id and CSLIT_Status = ''SHIPPED'' )  from customer_open_Order_lineItem cool, product, sales_order, customer where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and cool.COOL_Status<> ''SHIPPED'' and cool.COOL_Status<> ''CANCELLED'''
	set @ParamDefinition = N'@Prm_Product_ID varchar(15), @Prm_Customer_ID varchar(15), @Prm_Sales_Order_No varchar(15), @Prm_SO_Booking_Year varchar(15)'

	if ( @Product_ID IS NOT NULL AND ltrim(rtrim(@Product_ID)) <> '' AND @Product_ID <> '0' )
	begin
	  set @SQLString = @SQLString + ' and cool.product_id = @Prm_Product_ID'
	end
	
	if ( @Customer_ID IS NOT NULL AND ltrim(rtrim(@Customer_ID)) <> '' AND @Customer_ID <> '0' )
	begin
	  set @SQLString = @SQLString + ' and sales_order.customer_id = @Prm_Customer_ID'
	end
	
	if ( @Sales_Order_NO IS NOT NULL AND ltrim(rtrim(@Sales_Order_NO)) <> '' AND @Sales_Order_NO <> '0' )
	begin
	  set @SQLString = @SQLString + ' and sales_order.salesorder_no like @Prm_Sales_Order_No'
	  --set @Sales_Order_No = @Sales_Order_No + '%'
	end
	
	if ( @so_booking_year IS NOT NULL AND ltrim(rtrim(@so_booking_year)) <> '0' )
	begin
		set @SQLString = @SQLString + ' and year(sales_order.so_booking_date) = @Prm_SO_Booking_Year'
	end
	
	set @SQLString = @SQLString +  ' AND Sales_Order.SO_Is_Sample < 2' -- Added by Veeru ( 3rd Nov 2004 )

	set @SQLString = @SQLString + ' order by sales_order.salesorder_no, cool.lineitem_id'
	--print '***' + @SQLString + '***'
	
	execute sp_executesql @SQLString, @ParamDefinition, @Prm_Product_ID = @Product_ID, @Prm_Customer_ID = @Customer_ID, @Prm_Sales_Order_No = @Sales_Order_No, @Prm_SO_Booking_Year = @so_booking_year

	-- till here prabhanjan	
	declare @line_item_id int
	declare @@sales_order_number varchar (50)
	declare @req_date datetime
	declare @sch_date datetime
	declare @quantity int
	declare @quantityShipped int
	declare @unit_cost float
	declare @status varchar (20)
	declare @line_item_no int
	declare @product_id1 int
	declare @with_sub datetime
	declare @without_sub datetime
	declare @sales_order_id int
	declare @amount float
	declare @temp_customer_id int

	--declare @end_user varchar(50)
	--declare @end_user_id int
	declare @customer_partnumber varchar(50)
	declare @temp_so_booking_date datetime
	declare @odm_id int
	-- Prabhanjan (22 Mar 2004)
	--set @end_user = null

	--commented by varma (19 jul 2004)
	--select @end_user = SO_EndUser from Sales_Order where salesorder_no like @Sales_Order_No

	open lineItemCursor 
		
	fetch next from lineItemCursor into  @line_item_id,@@sales_order_number,@req_date,@sch_date,@quantity,@unit_cost,@status,@line_item_no,@product_id1,@with_sub,@without_sub, @customer_partnumber, @sales_order_id,@amount, @temp_customer_id, @temp_so_booking_date, @odm_id, @quantityShipped
	while @@fetch_status = 0
	begin
	--Added by varma (19 jul 2004)
	/*select @end_user = SO_EndUser from Sales_Order where salesorder_no like @@sales_order_number+'%'
	set @customer_partnumber = null
	if( ltrim(rtrim(@end_user)) = '' or @end_user is null )
	begin	
		select @customer_partnumber = Customer_PartNumber from Customer_PartNumbers where Product_id = @product_id1 and Customer_ID = @temp_customer_id and Odm_Id is NULL
	end
	else
	begin
		SELECT @end_user_id = End_User_ID from End_User_Table where E_Name = @end_user
		select @customer_partnumber = Customer_PartNumber from End_User_Part_Numbers where Product_ID = @product_id1 and End_User_ID = @end_user_id
	end*/
	if @quantityShipped >  0
	begin
		SET @quantity = @quantity - @quantityShipped
	end
	
	if @quantity > 0
	begin
		insert into @results values (@line_item_id,@@sales_order_number,@req_date,@sch_date,@quantity,@unit_cost,@status,@line_item_no,@product_id1,@with_sub,@without_sub,@sales_order_id,@amount, @temp_customer_id, @customer_partnumber, @temp_so_booking_date, @odm_id)
	end

	set @line_item_id = null
	set @@sales_order_number  = null
	set @req_date = null
	set @sch_date = null
	set @quantity = null
	set @unit_cost = null
	set @status  = null
	set @line_item_no = null
	set @product_id1 = null
	set @with_sub = null
	set @without_sub = null
	set @sales_order_id  = null
	set @amount = null
	set @temp_customer_id = null
	set @temp_so_booking_date = null
	set @odm_id = null
	fetch next from lineItemCursor into  @line_item_id,@@sales_order_number,@req_date,@sch_date,@quantity,@unit_cost,@status,@line_item_no,@product_id1,@with_sub,@without_sub, @customer_partnumber, @sales_order_id,@amount, @temp_customer_id, @temp_so_booking_date, @odm_id, @quantityShipped
 end
 close lineItemCursor
 deallocate lineItemCursor
select * from @results


